package com.me.demotable;

import com.fasterxml.jackson.annotation.JsonIgnore;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.datatype.jsr310.JavaTimeModule;
import com.fasterxml.jackson.datatype.jsr310.deser.LocalDateTimeDeserializer;
import com.fasterxml.jackson.datatype.jsr310.ser.LocalDateTimeSerializer;
import com.google.common.base.CaseFormat;
import com.google.common.collect.Lists;
import com.google.common.collect.Sets;
import com.google.common.io.Files;
import com.querydsl.codegen.GenericExporter;
import com.querydsl.codegen.Keywords;
import com.querydsl.core.Tuple;
import com.querydsl.core.types.Expression;
import com.querydsl.core.types.OrderSpecifier;
import com.querydsl.core.types.dsl.Expressions;
import com.querydsl.jpa.impl.JPAQueryFactory;
import com.squareup.javapoet.*;

import lombok.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.Cache;
import org.springframework.cache.CacheManager;
import org.springframework.cache.annotation.EnableCaching;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.querydsl.QPageRequest;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;


import javax.lang.model.element.Modifier;
import javax.persistence.*;
import javax.tools.ToolProvider;
import java.io.File;
import java.io.IOException;
import java.io.Serializable;
import java.lang.reflect.ParameterizedType;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.*;
import java.time.Instant;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 使用说明: 添加以下依赖,然后修改此文件中的main函数,填写数据库用户名，密码，之后run

 <dependency>
 <groupId>javax.inject</groupId>
 <artifactId>javax.inject</artifactId>
 <version>1</version>
 </dependency>

 <dependency>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-data-jpa</artifactId>
 </dependency>
 <dependency>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-web</artifactId>
 </dependency>

 <dependency>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-devtools</artifactId>
 <scope>runtime</scope>
 </dependency>
 <dependency>
 <groupId>mysql</groupId>
 <artifactId>mysql-connector-java</artifactId>
 <scope>runtime</scope>
 </dependency>
 <dependency>
 <groupId>org.projectlombok</groupId>
 <artifactId>lombok</artifactId>
 <optional>true</optional>
 </dependency>

 <dependency>
 <groupId>com.querydsl</groupId>
 <artifactId>querydsl-core</artifactId>
 </dependency>

 <dependency>
 <groupId>com.querydsl</groupId>
 <artifactId>querydsl-jpa</artifactId>
 </dependency>

 <dependency>
 <groupId>com.squareup</groupId>
 <artifactId>javapoet</artifactId>
 <version>1.11.1</version>
 </dependency>

 <dependency>
 <groupId>com.querydsl</groupId>
 <artifactId>querydsl-codegen</artifactId>
 <version>4.2.1</version>
 </dependency>

 *
 * Created by linxy on 2017/4/28.
 */
public class TableFactory {

    /**
     * 缓存field名字
     */
    public static String CFN = "cacheManager";

    /**
     * 实体类field名字，只有当启用缓存时才需要生成这个field
     */
    public static String EFN = "entityName";

    /**
     * dao field名字
     */
    public static String DFN = "dao";

    public static String PRIMARY_KEY_METHOD_NAME = "getPrimaryKey";

    public static String BASE_ENTITY_CLASS_NAME = "BaseEntity";
    public static String QUERYDSL_CONFIG_CLASS_NAME = "QueryDslConfig";
    public static String BASE_SERVICE_CLASS_NAME = "BaseService";
    public static String UTILS_CLASS_NAME = "Utils";
    public static String BASE_SERVICE_IMPL_CLASS_NAME = BASE_SERVICE_CLASS_NAME + "Impl";

    /**
     * 泛型ID
     */
    public static ClassName ID = ClassName.get("", "ID");

    /**
     * 泛型T
     */
    public static ClassName ENTITY = ClassName.get("", "T");

    /**
     * 泛型T
     */
    public static ClassName T = ENTITY;

    /**
     * 泛型DAO
     */
    public static ClassName DAO = ClassName.get("", "DAO");

    /**
     * 是否启用缓存
     */
    public static boolean userCacheManager = false;

    /**
     * 是否添加 qdsl包后缀
     */
    public static boolean useQdslPackageSuffix = true;
//    public static boolean overWriteBase = false;
//    public static boolean overWriteConfig = false;

    /**
     * 包含的表名
     */
    public static String[] includeTables = null;

    /**
     * 排除的表名,当与includeTables同时使用时，只有includeTables生效
     */
    public static String[] excludeTables = null;



    public static String url;
    public static String username;
    public static String password;



    public static class Table {
        /**
         * 表名
         */
        private String tableName;

        /**
         * 注释
         */
        private String comment;

        /**
         * 实体名
         */
        private String javaBeanName;

        /**
         * 主键列
         */
        private Column primaryColumn;

        /**
         * 所有列
         */
        private List<Column> columns = new ArrayList<Column>();

        public static class ParameterInfo {
            private ClassName typeName;
            private String name;

            public ParameterInfo(ClassName typeName, String name) {
                this.typeName = typeName;
                this.name = name;
            }
        }

        public static class Column {

            /**
             * 列类型
             */
            private Class<?> type;

            /**
             * 列名
             */
            private String columnName;

            /**
             * 实体属性名
             */
            private String fieldName;

            /**
             * 注释
             */
            private String comment;

            /**
             * 是否是主键
             */
            private boolean isPrimaryKey;

            /**
             * 是否是自增长,一般是主键
             */
            private boolean isAutoInc;

            /**
             * 是否为空
             */
            private boolean isNullable;

            public Column(Class<?> type, String columnName, String comment, boolean isPrimaryKey) {
                this.type = type;
                this.columnName = columnName;
                this.comment = comment;
                this.isPrimaryKey = isPrimaryKey;

                fieldName = CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.LOWER_CAMEL, columnName);
            }
        }

        private static Class<?> getClassType(int type) {
            Class<?> clazz = String.class;

            switch (type) {

                case Types.NUMERIC:
                case Types.DECIMAL:
                    clazz = java.math.BigDecimal.class;
                    break;

                case Types.BIT:
                    clazz = Boolean.class;
                    break;

                case Types.TINYINT:
                    clazz = Byte.class;
                    break;

                case Types.SMALLINT:
                    clazz = Short.class;
                    break;

                case Types.INTEGER:
                    clazz = Integer.class;
                    break;

                case Types.BIGINT:
                    clazz = Long.class;
                    break;

                case Types.REAL:
                    clazz = Float.class;
                    break;

                case Types.FLOAT:
                    clazz = Float.class;
                    break;

                case Types.DOUBLE:
                    clazz = Double.class;
                    break;

                case Types.BINARY:
                case Types.VARBINARY:
                case Types.LONGVARBINARY:
                    try {
                        clazz = Class.forName("byte[]");
                    } catch (ClassNotFoundException e) {
                        e.printStackTrace();
                    }
                    break;

                case Types.DATE:
                    clazz = LocalDate.class;
                    break;

                case Types.TIME:
                    clazz = LocalDateTime.class;
                    break;

                case Types.TIMESTAMP:
                    clazz = LocalDateTime.class;
                    break;

                case Types.BLOB:
                    clazz = Blob.class;
                    break;

                case Types.CLOB:
                    clazz = Clob.class;
                    break;
            }

            return clazz;
        }


        public Table(String tableName, String eraseTablePrefix) {
            this.tableName = tableName;
            javaBeanName = tableName;
            if(eraseTablePrefix != null && !"".equals(eraseTablePrefix) && tableName.startsWith(eraseTablePrefix)) {
                javaBeanName = CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.UPPER_CAMEL, javaBeanName.substring(eraseTablePrefix.length()));
            }
            javaBeanName = CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.UPPER_CAMEL, javaBeanName);
        }

        private void populateComment(Connection connection) throws SQLException {
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery("SHOW CREATE TABLE " + tableName);
            if(rs.next()) {
                String create = rs.getString(2);
                int index = create.indexOf("COMMENT='");
                if(index < 0) {
                    return;
                }
                comment = create.substring(index+9);
                comment = comment.substring(0,comment.length() - 1);
            }
        }


        public void populateColumns(Connection connection) throws SQLException {

            ResultSet resultSet;
            String primaryKeyColumnName = null;

            DatabaseMetaData metaData = connection.getMetaData();

            populateComment(connection);

            resultSet = metaData.getPrimaryKeys("", "", tableName);
            while (resultSet.next()) {
                primaryKeyColumnName = resultSet.getString("COLUMN_NAME");
            }

            resultSet = metaData.getColumns("", "", tableName, "%");
            while (resultSet.next()) {
                String columnName = resultSet.getString("COLUMN_NAME");
                String remark = resultSet.getString("REMARKS");
                String dataType = resultSet.getString("DATA_TYPE");
                String isNullable = resultSet.getString("IS_NULLABLE");
                String isAutoIncrement = resultSet.getString("IS_AUTOINCREMENT");

                boolean isPrimaryKey = columnName.equals(primaryKeyColumnName);

                Column column = new Column(getClassType(Integer.parseInt(dataType)), columnName, remark, isPrimaryKey);
                column.isAutoInc = "YES".equals(isAutoIncrement);
                if(isPrimaryKey) {
                    primaryColumn = column;
                }
                this.columns.add(column);
            }
        }

        private String daoName() {
            return javaBeanName + "Repository";
        }

        private String serviceName() {
            return javaBeanName + "Service";
        }

        private String serviceImplName() {
            return serviceName() + "Impl";
        }

        private ClassName entityClassName(String packageName) {
            return ClassName.get(entityPackageName(packageName), javaBeanName);
        }

        public void createEntity(String dir, String packages) throws IOException {
            TypeSpec.Builder entity = TypeSpec.classBuilder(javaBeanName);
            entity.addModifiers(Modifier.PUBLIC);
            entity.addSuperinterface(Serializable.class);
            if(primaryColumn != null) {
                entity.addSuperinterface(ParameterizedTypeName.get(ClassName.get(basePackageName(packages), baseEntityName()), ClassName.get(primaryColumn.type)));
            }
            entity.addAnnotation(primaryColumn != null ? Entity.class : Embeddable.class);
            entity.addAnnotation(AnnotationSpec.builder(javax.persistence.Table.class).addMember("name", "$S", tableName).build());
            entity.addAnnotation(Data.class);
            entity.addAnnotation(ToString.class);
            entity.addJavadoc("$S", this.comment);

            entity.addField(FieldSpec.builder(ClassName.LONG, "serialVersionUID", Modifier.PRIVATE, Modifier.STATIC, Modifier.FINAL)
                    .initializer("$N", "1L")
                    .build());

            columns.forEach(c -> {
                FieldSpec.Builder field = FieldSpec.builder(c.type, c.fieldName, Modifier.PRIVATE);
                if(c.isPrimaryKey) {
                    field.addAnnotation(Id.class);
                    if(c.isAutoInc) {
                        field.addAnnotation(AnnotationSpec.builder(GeneratedValue.class)
                                .addMember("strategy", "$T.IDENTITY", GenerationType.class)
                                .build());
                    }
                }
                field.addJavadoc("$S", c.comment);
                field.addAnnotation(AnnotationSpec.builder(javax.persistence.Column.class).addMember("name", "$S", c.columnName).build());
                entity.addField(field.build());
            });

            if(primaryColumn != null) {
                entity.addMethod(MethodSpec.methodBuilder(PRIMARY_KEY_METHOD_NAME)
                        .addAnnotation(JsonIgnore.class)
                        .addModifiers(Modifier.PUBLIC)
                        .returns(primaryColumn.type)
                        .addStatement("return this.$N", primaryColumn.fieldName)
                        .build());
            }

            JavaFile.builder(entityPackageName(packages), entity.build()).build().writeTo(Paths.get(dir));
        }

        public void createDao(String dir, String packages) throws IOException, ClassNotFoundException {

            if(primaryColumn == null) {
                System.out.println(javaBeanName + " no primary key");
                return;
            }

            if(Paths.get(dir, packageToPath(daoPackageName(packages)), daoName()+".java").toFile().exists()) {
                return;
            }

            TypeSpec.Builder dao = TypeSpec.interfaceBuilder(daoName());
            dao.addModifiers(Modifier.PUBLIC);
            dao.addSuperinterface(ParameterizedTypeName.get(ClassName.get(JpaRepository.class), entityClassName(packages), ClassName.get(primaryColumn.type)));
            dao.addSuperinterface(ParameterizedTypeName.get(ClassName.get(QuerydslPredicateExecutor.class), entityClassName(packages)));

            JavaFile.builder(daoPackageName(packages), dao.build()).build().writeTo(Paths.get(dir));
        }

        public void createService(String dir, String packages) throws IOException {

            if(primaryColumn == null) {
                return;
            }

            // service 接口类

            TypeSpec.Builder srv = TypeSpec.interfaceBuilder(serviceName());
            srv.addModifiers(Modifier.PUBLIC);
            srv.addSuperinterface(ParameterizedTypeName.get(ClassName.get(basePackageName(packages), baseServiceName()), entityClassName(packages), ClassName.get(primaryColumn.type)));

            // serviceImpl 实现类
            TypeSpec.Builder srvImpl = TypeSpec.classBuilder(serviceImplName());
            srvImpl.addModifiers(Modifier.PUBLIC);
            srvImpl.superclass(ParameterizedTypeName.get(ClassName.get(basePackageName(packages), baseServiceImplName()), entityClassName(packages), ClassName.get(primaryColumn.type), ClassName.get(daoPackageName(packages), daoName())));
            srvImpl.addSuperinterface(ClassName.get(servicePackageName(packages), serviceName()));
            srvImpl.addAnnotation(Service.class);
            srvImpl.addAnnotation(AnnotationSpec.builder(Transactional.class)
                    .addMember("readOnly", "$N", "true")
                    .build());

            srvImpl.addMethod(MethodSpec.constructorBuilder()
                    .addAnnotation(Autowired.class)
                    .addModifiers(Modifier.PUBLIC)
                    .build());

            writeFile(dir, servicePackageName(packages), srv.build());
            writeFile(dir, serviceImplPackageName(packages), srvImpl.build());
        }
    }


    // 以下是创建公共部分

    public static void createQueryDslConfig(String dir, String packageName) throws IOException {

        TypeSpec.Builder cfg = TypeSpec.classBuilder(queryDslConfigName());
        cfg.addModifiers(Modifier.PUBLIC);
        cfg.addAnnotation(Data.class);
        cfg.addAnnotation(Configuration.class);

        if(userCacheManager) {
            cfg.addAnnotation(EnableCaching.class);
        }

        cfg.addField(FieldSpec.builder(DateTimeFormatter.class, "FORMATTER")
                .addModifiers(Modifier.PUBLIC, Modifier.STATIC)
                .initializer("$L", "DateTimeFormatter.ofPattern(\"yyyy-MM-dd HH:mm:ss\")")
                .build());

        cfg.addField(FieldSpec.builder(EntityManager.class, "em")
                .addAnnotation(PersistenceContext.class)
                .build());

        cfg.addMethod(MethodSpec.methodBuilder("jpaQueryFactory")
                .addAnnotation(Bean.class)
                .addModifiers(Modifier.PUBLIC)
                .addStatement("return new JPAQueryFactory($N)", "em")
                .returns(JPAQueryFactory.class)
                .build());

        cfg.addMethod(MethodSpec.methodBuilder("serializingObjectMapper")
                .addAnnotation(Bean.class)
                .addAnnotation(Primary.class)
                .returns(ObjectMapper.class)
                .addStatement("$T objectMapper = new ObjectMapper()", ObjectMapper.class)
                .addStatement("$T javaTimeModule = new JavaTimeModule()", JavaTimeModule.class)
                .addStatement("$N.addSerializer($T.class, new $T(FORMATTER))", "javaTimeModule", LocalDateTime.class, LocalDateTimeSerializer.class)
                .addStatement("$N.addDeserializer($T.class, new $T(FORMATTER))", "javaTimeModule", LocalDateTime.class, LocalDateTimeDeserializer.class)
                .addStatement("$N.registerModule($N)", "objectMapper", "javaTimeModule")
                .addStatement("return objectMapper")
                .build());

        writeFile(dir, configPackageName(packageName), cfg.build());
    }



    public static void createUtils(String dir, String packageName) throws IOException {

        TypeSpec type = null;
        Path path = null;
        TypeSpec.Builder builder = null;

        ClassName ERROR_CODE = ClassName.get(utilsPackageName(packageName), "ErrorCode");
        ClassName REST_RESULT = ClassName.get(utilsPackageName(packageName), "RestResult");

        builder = TypeSpec.enumBuilder(ERROR_CODE.simpleName())
                .addAnnotation(Getter.class)
                .addModifiers(Modifier.PUBLIC)
                .addField(String.class, "msg", Modifier.PRIVATE)
                .addField(ClassName.INT, "code", Modifier.PRIVATE)
                .addMethod(MethodSpec.constructorBuilder()
                        .addParameter(ClassName.INT, "code")
                        .addParameter(String.class, "msg")
                        .addStatement("this.code = code")
                        .addStatement("this.msg = msg").build())
                .addEnumConstant("ERROR_OK", TypeSpec.anonymousClassBuilder("0, \"成功\"").build());

        writeFile(dir, utilsPackageName(packageName), builder.build());

        builder = TypeSpec.classBuilder(REST_RESULT.simpleName())
                .addModifiers(Modifier.PUBLIC)
                .addTypeVariable(TypeVariableName.get(T.simpleName()))
                .addSuperinterface(Serializable.class)
                .addAnnotation(Data.class)
                .addAnnotation(NoArgsConstructor.class)
                .addAnnotation(AllArgsConstructor.class)
                .addField(FieldSpec.builder(ClassName.LONG, "serialVersionUID")
                        .addModifiers(Modifier.PRIVATE, Modifier.STATIC, Modifier.FINAL).initializer("1L").build())
                .addField(ClassName.INT, "code", Modifier.PRIVATE)
                .addField(String.class, "msg", Modifier.PRIVATE)
                .addField(T, "data", Modifier.PRIVATE)

                .addMethod(MethodSpec.methodBuilder("ok")
                        .addModifiers(Modifier.PUBLIC, Modifier.STATIC)
                        .addTypeVariable(TypeVariableName.get(T.simpleName()))
                        .addParameter(T, "data")
                        .returns(ParameterizedTypeName.get(REST_RESULT, T))
                        .addStatement("return error(ErrorCode.ERROR_OK, data)")
                        .build())

                .addMethod(MethodSpec.methodBuilder("error")
                        .addModifiers(Modifier.PUBLIC, Modifier.STATIC)
                        .addAnnotation(AnnotationSpec.builder(SuppressWarnings.class).addMember("value", "$S", "unchecked").build())
                        .addTypeVariable(TypeVariableName.get(T.simpleName()))
                        .addParameter(ERROR_CODE, "errorCode")
                        .addParameter(T, "data")
                        .returns(ParameterizedTypeName.get(REST_RESULT, T))
                        .addStatement("return new RestResult(errorCode.getCode(), errorCode.getMsg(), data)")
                        .build());

        writeFile(dir, utilsPackageName(packageName), builder.build());

        builder = TypeSpec.classBuilder("PageResult")
                .addSuperinterface(Serializable.class)
                .addModifiers(Modifier.PUBLIC)
                .addTypeVariable(TypeVariableName.get(T.simpleName()))
                .addAnnotation(Builder.class)
                .addAnnotation(Data.class)
                .addField(ClassName.INT, "totalPage", Modifier.PRIVATE, Modifier.FINAL)
                .addField(ClassName.LONG, "totalElement", Modifier.PRIVATE, Modifier.FINAL)
                .addField(ClassName.INT, "perPageSize", Modifier.PRIVATE, Modifier.FINAL)
                .addField(ClassName.INT, "pageNumber", Modifier.PRIVATE, Modifier.FINAL)
                .addField(ParameterizedTypeName.get(ClassName.get(List.class), T), "content", Modifier.PRIVATE, Modifier.FINAL)
                .addMethod(MethodSpec.methodBuilder("toPageResult")
                        .addModifiers(Modifier.PUBLIC, Modifier.STATIC)
                        .addTypeVariable(TypeVariableName.get(T.simpleName()))
                        .addParameter(ParameterizedTypeName.get(ClassName.get(Page.class), T), "page")
                        .returns(ClassName.get("", "PageResult"))
                        .addCode("return PageResult.<T>builder()")
                        .addCode(".totalPage(page.getTotalPages())")
                        .addCode(".pageNumber(page.getNumber())")
                        .addCode(".totalElement(page.getTotalElements())")
                        .addCode(".perPageSize(page.getSize())")
                        .addCode(".content(page.getContent())")
                        .addStatement(".build()")
                        .build());

        writeFile(dir, utilsPackageName(packageName), builder.build());

        builder = TypeSpec.classBuilder("QdslTools")
                .addModifiers(Modifier.PUBLIC)
                .addField(FieldSpec.builder(ClassName.INT, "DEFAULT_PAGE_SIZE")
                        .addModifiers(Modifier.PUBLIC, Modifier.STATIC)
                        .initializer("10")
                        .build())
                .addField(FieldSpec.builder(DateTimeFormatter.class, "FORMATTER")
                        .addModifiers(Modifier.PUBLIC, Modifier.STATIC)
                        .initializer("$L", "DateTimeFormatter.ofPattern(\"yyyy-MM-dd HH:mm:ss\")")
                        .build())
                .addMethod(MethodSpec.methodBuilder("toLocalDateTime")
                        .addModifiers(Modifier.PUBLIC, Modifier.STATIC)
                        .returns(LocalDateTime.class)
                        .addParameter(ClassName.LONG, "millis")
                        .addStatement("return $T.ofEpochMilli(millis).atZone($T.systemDefault()).toLocalDateTime()", Instant.class, ZoneId.class)
                        .build())
                .addMethod(MethodSpec.methodBuilder("toLocalDateTime")
                        .addModifiers(Modifier.PUBLIC, Modifier.STATIC)
                        .returns(LocalDateTime.class)
                        .addParameter(String.class, "time")
                        .addStatement("return $T.parse(time, FORMATTER)", LocalDateTime.class)
                        .build())

                .addMethod(MethodSpec.methodBuilder("toExpression")
                        .addModifiers(Modifier.PUBLIC, Modifier.STATIC)
                        .addParameter(ClassName.LONG, "millis")
                        .addStatement("return $T.constant(toLocalDateTime(millis))", Expressions.class)
                        .returns(ParameterizedTypeName.get(Expression.class, LocalDateTime.class))
                        .build())
                .addMethod(MethodSpec.methodBuilder("toExpression")
                        .addModifiers(Modifier.PUBLIC, Modifier.STATIC)
                        .addParameter(String.class, "time")
                        .addStatement("return $T.constant(toLocalDateTime(time))", Expressions.class)
                        .returns(ParameterizedTypeName.get(Expression.class, LocalDateTime.class))
                        .build())
                .addMethod(MethodSpec.methodBuilder("getOrDefault")
                        .addModifiers(Modifier.PUBLIC, Modifier.STATIC)
                        .addTypeVariable(TypeVariableName.get(T.simpleName()))
                        .addParameter(Tuple.class, "tuple")
                        .addParameter(ParameterizedTypeName.get(ClassName.get(Expression.class), T), "e")
                        .addParameter(T, "val")
                        .addCode(CodeBlock.builder()
                                .beginControlFlow("if(tuple == null)")
                                .addStatement("return val")
                                .endControlFlow()
                                .build())
                        .addStatement("return $T.ofNullable(tuple.get(e)).orElse(val)", Optional.class)
                        .returns(T)
                        .build())
                .addMethod(MethodSpec.methodBuilder("getOrDefault")
                        .addModifiers(Modifier.PUBLIC, Modifier.STATIC)
                        .addTypeVariable(TypeVariableName.get(T.simpleName()))
                        .addParameter(Tuple.class, "tuple")
                        .addParameter(ClassName.INT, "idx")
                        .addParameter(ParameterizedTypeName.get(ClassName.get(Class.class), T), "clazz")
                        .addParameter(T, "val")
                        .addCode(CodeBlock.builder()
                                .beginControlFlow("if(tuple == null)")
                                .addStatement("return val")
                                .endControlFlow()
                                .build())
                        .addStatement("return $T.ofNullable(tuple.get(idx, clazz)).orElse(val)", Optional.class)
                        .returns(T)
                        .build())
                .addMethod(MethodSpec.methodBuilder("toPageRequest")
                        .addModifiers(Modifier.PUBLIC, Modifier.STATIC)
                        .addParameter(Integer.class, "page")
                        .addParameter(Integer.class, "pageSize")
                        .addParameter(OrderSpecifier[].class, "orderSpecifiers")
                        .varargs(true)
                        .returns(QPageRequest.class)
                        .addStatement("page = (page == null || page <= 0) ? 0 : page - 1")
                        .addStatement("pageSize = (pageSize == null || pageSize <= 0) ? DEFAULT_PAGE_SIZE : pageSize")
                        .addStatement("return new $T(page, pageSize, orderSpecifiers)", QPageRequest.class)
                        .build());

        writeFile(dir, utilsPackageName(packageName), builder.build());
    }

    public static void createBaseService(String dir, String packageName) throws IOException {

        TypeSpec.Builder srv = TypeSpec.interfaceBuilder(baseServiceName());

        srv.addModifiers(Modifier.PUBLIC);
        srv.addTypeVariable(TypeVariableName.get(ENTITY.simpleName()));
        srv.addJavadoc("$S", "自动生成文件，请不要修改");
        srv.addTypeVariable(TypeVariableName.get(ID.simpleName(), Serializable.class));

        TypeSpec.Builder srvImpl = TypeSpec.classBuilder(baseServiceImplName());
        srvImpl.addModifiers(Modifier.PUBLIC);
        srv.addJavadoc("$S", "自动生成文件，请不要修改");
        srvImpl.addTypeVariable(TypeVariableName.get(ENTITY.simpleName(), ParameterizedTypeName.get(ClassName.get(basePackageName(packageName), baseEntityName()), ID)));
        srvImpl.addTypeVariable(TypeVariableName.get(ID.simpleName(), Serializable.class));
        srvImpl.addTypeVariable(TypeVariableName.get(DAO.simpleName(),
                ParameterizedTypeName.get(ClassName.get(JpaRepository.class), ENTITY, ID),
                ParameterizedTypeName.get(ClassName.get(QuerydslPredicateExecutor.class), ENTITY)));

        srvImpl.addField(FieldSpec.builder(DAO, DFN)
                .addModifiers(Modifier.PROTECTED)
                .addAnnotation(Autowired.class)
                .build());

        srvImpl.addField(FieldSpec.builder(JPAQueryFactory.class, "jpaQueryFactory")
                .addAnnotation(Autowired.class)
                .addModifiers(Modifier.PROTECTED)
                .build());

        if(userCacheManager) {
            srvImpl.addField(FieldSpec.builder(CacheManager.class, CFN)
                    .addAnnotation(Autowired.class)
                    .addModifiers(Modifier.PRIVATE).build());

            srvImpl.addField(FieldSpec.builder(String.class, EFN)
                    .addModifiers(Modifier.PRIVATE).build());
        }

        MethodSpec.Builder constructor = MethodSpec.constructorBuilder()
                .addModifiers(Modifier.PUBLIC);

        if(userCacheManager) {
            constructor.addStatement("this.$N = (($T) getClass().getGenericSuperclass()).getActualTypeArguments()[0].getTypeName()", EFN, ParameterizedType.class);
        }

        srvImpl.addMethod(constructor.build());

        // 下面是添加一些常用的查询方法

        MethodSpec.Builder method = null;

        // save
        method = createServiceMethod(srv, "save", ENTITY,
                Lists.newArrayList(new Table.ParameterInfo(ENTITY, "val")))
                .addAnnotation(Transactional.class)
                .addAnnotation(Modifying.class)
                .addStatement("val = $N.save(val)", DFN);
        if(userCacheManager) {
            method.addStatement("$T.requireNonNull($N.getCache($N)).put(val.$N(), val)", Objects.class, CFN, EFN, PRIMARY_KEY_METHOD_NAME);
        }
        srvImpl.addMethod(method.addStatement("return val").build());

        // findById
        method = createServiceMethod(srv, "findById", ENTITY,
                Lists.newArrayList(new Table.ParameterInfo(ID, "id")))
                .addAnnotation(AnnotationSpec.builder(SuppressWarnings.class).addMember("value", "$S", "unchecked").build());
        if(userCacheManager) {
            srvImpl.addMethod(method.addStatement("T val = null")
                    .addStatement("$T valueWrapper = $T.requireNonNull($N.getCache($N)).get(id)", Cache.ValueWrapper.class, Objects.class, CFN, EFN)
                    .addCode(CodeBlock.builder().beginControlFlow("if(valueWrapper != null)")
                            .addStatement("val = (T)valueWrapper.get()").endControlFlow().build())
                    .addCode(CodeBlock.builder()
                            .beginControlFlow("if(val == null)")
                            .addStatement("val = $N.findById(id).orElse(null)", DFN)
                            .beginControlFlow("if(val != null)")
                            .addStatement("$T.requireNonNull($N.getCache($N)).put(id, val)", Objects.class, CFN, EFN)
                            .endControlFlow()
                            .endControlFlow()
                            .build())
                    .addStatement("return val")
                    .build());
        } else {
            srvImpl.addMethod(method.addStatement("return $N.findById(id).orElse(null)", DFN).build());
        }

        // deleteById
        method = createServiceMethod(srv, "deleteById", ClassName.VOID,
                Lists.newArrayList(new Table.ParameterInfo(ID, "id")))
                .addAnnotation(Transactional.class)
                .addAnnotation(Modifying.class)
                .addStatement("$N.deleteById(id)", DFN);

        if(userCacheManager) {
            srvImpl.addMethod(method.addStatement("$T.requireNonNull($N.getCache($N)).evict(id)", Objects.class, CFN, EFN).build());
        } else {
            srvImpl.addMethod(method.build());
        }

        // findAll
        srvImpl.addMethod(createServiceMethod(srv, "findAll", ParameterizedTypeName.get(ClassName.get(List.class), ENTITY),
                Collections.emptyList())
                .addStatement("return $N.findAll()", DFN).build());

        // findAll(page)
        srvImpl.addMethod(createServiceMethod(srv, "findAll", ParameterizedTypeName.get(ClassName.get(Page.class), ENTITY),
                Lists.newArrayList(new Table.ParameterInfo(ClassName.get(Pageable.class), "page")))
                .addStatement("return $N.findAll($N)", DFN, "page").build());

        if(userCacheManager) {
            srvImpl.addMethod(createServiceMethod(srv, "evict", ClassName.VOID,
                    Lists.newArrayList(new Table.ParameterInfo(ID, "id")))
                    .beginControlFlow("if($N != null)", CFN)
                    .addStatement("$T.requireNonNull($N.getCache($N)).evict(id)", Objects.class, CFN, EFN)
                    .endControlFlow()
                    .build());
        }

        writeFile(dir, basePackageName(packageName), srv.build());
        writeFile(dir, basePackageName(packageName), srvImpl.build());
    }

    public static void createBaseEntity(String dir, String packages) throws IOException {
        TypeSpec.Builder baseEntity = TypeSpec.interfaceBuilder(baseEntityName());
        baseEntity.addModifiers(Modifier.PUBLIC);
        baseEntity.addTypeVariable(TypeVariableName.get("ID", Serializable.class));

        baseEntity.addMethod(MethodSpec.methodBuilder(PRIMARY_KEY_METHOD_NAME)
                .addModifiers(Modifier.PUBLIC,  Modifier.DEFAULT)
                .returns(ID)
                .addStatement("return null")
                .build());

        writeFile(dir, basePackageName(packages), baseEntity.build());
    }

    public static List<String> getAllFiles(String dir, String... packageNames) {
        List<String> results = Lists.newArrayList();
        for(String p : packageNames) {
            results.addAll(Lists.newArrayList(Files.fileTreeTraverser().children(Paths.get(dir, p.replaceAll("\\.", "/")).toFile())
                    .iterator()).stream().filter(File::isFile).map(File::getAbsolutePath).collect(Collectors.toList()));
        }
        return results;
    }

    /**
     * 生成 enity, dao, service, service impl,如何文件存在，则不创建
     * @param dir 生成代码目录
     * @param packages 包名
     * @throws SQLException
     * @throws IOException
     */
    public void createJavaFiles(String dir, String packages) throws SQLException, IOException, ClassNotFoundException {

        // 防止覆盖原有的
        if(useQdslPackageSuffix && !packages.endsWith(".qdsl")) {
            packages += ".qdsl";
        }

        createUtils(dir, packages);
        createQueryDslConfig(dir, packages);
        createBaseEntity(dir, packages);
        createBaseService(dir, packages);

        List<Table> tables = fetchAllTable();

        if(includeTables != null) {
            Set<String> includeTables = Sets.newHashSet(TableFactory.includeTables);
            tables = tables.stream().filter(t -> includeTables.contains(t.tableName)).collect(Collectors.toList());
        } else if(excludeTables != null) {
            Set<String> includeTables = Sets.newHashSet(excludeTables);
            tables = tables.stream().filter(t -> !includeTables.contains(t.tableName)).collect(Collectors.toList());
        }

        for(Table t : tables) {
            t.createEntity(dir, packages);
            t.createDao(dir, packages);
            t.createService(dir, packages);
        }

        // 遍历所有实体文件
        List<String> paths = getAllFiles(dir, entityPackageName(packages), basePackageName(packages));

        List<String> args = Lists.newArrayListWithCapacity(paths.size() + 2);

        String targetDir = "target/classes";

        if(dir.endsWith("src/main/java")) {
            targetDir = dir.substring(0, dir.indexOf("src/main/java")) + targetDir;
        }

        // 添加输出目录
        args.add("-d");
        args.add(Paths.get(targetDir).toString());
        args.addAll(paths);

        // 由于手动生成的文件没有进行compiler,而 GenericExporter 是扫瞄已编译好的文件，所以这里手动编译一下
        ToolProvider.getSystemJavaCompiler().run(null, null, null, args.toArray(new String[]{}));

        // 生成Q
        GenericExporter exporter = new GenericExporter();
        exporter.setKeywords(Keywords.JPA);
        exporter.setEntityAnnotation(Entity.class);
        exporter.setEmbeddableAnnotation(Embeddable.class);
        exporter.setEmbeddedAnnotation(Embedded.class);
        exporter.setSupertypeAnnotation(MappedSuperclass.class);
        exporter.setSkipAnnotation(Transient.class);
        exporter.setTargetFolder(new File(dir));
        exporter.export(entityPackageName(packages));
    }

    public List<Table> fetchAllTable() throws SQLException {
        Connection conn = DriverManager.getConnection(url, username, password);
        DatabaseMetaData md = conn.getMetaData();
        ResultSet rs = md.getTables(null, null, "%", null);
        List<Table> tables = new ArrayList<>();
        while (rs.next()) {
            Table table = new Table(rs.getString(3), "");
            table.populateColumns(conn);
            tables.add(table);
        }
        return tables;
    }

    public static String basePackageName(String packageName) {
        return packageName + ".base";
    }

    public static String entityPackageName(String packageName) {
        return packageName + ".entity";
    }

    public static String daoPackageName(String packageName) {
        return packageName + ".repository";
    }

    public static String servicePackageName(String packageName) {
        return packageName + ".service";
    }

    public static String serviceImplPackageName(String packageName) {
        return servicePackageName(packageName) + ".impl";
    }

    public static String configPackageName(String packageName) {
        return packageName + ".config";
    }

    public static String utilsPackageName(String packageName) {
        return packageName + ".utils";
    }

    public static String packageToPath(String packageName) {
        return packageName.replaceAll("\\.", "/");
    }

    public static String queryDslConfigName() {
        return QUERYDSL_CONFIG_CLASS_NAME;
    }

    public static String utilsName() {
        return UTILS_CLASS_NAME;
    }

    public static String baseEntityName() { return BASE_ENTITY_CLASS_NAME; }

    public static String baseServiceName() {
        return BASE_SERVICE_CLASS_NAME;
    }

    public static String baseServiceImplName() {
        return BASE_SERVICE_IMPL_CLASS_NAME;
    }


    public static void writeFile(String dir, String packageName, TypeSpec type) throws IOException {
        Path path = Paths.get(dir, packageToPath(packageName), type.name + ".java");
        if(!path.toFile().exists()) {
            JavaFile.builder(packageName, type).skipJavaLangImports(true).build().writeTo(Paths.get(dir));
        }
    }


    public static MethodSpec.Builder createServiceMethod(TypeSpec.Builder srv,
                                                         String name,
                                                         TypeName returnType,
                                                         List<Table.ParameterInfo> ps) {

        MethodSpec.Builder method = MethodSpec.methodBuilder(name)
                .addModifiers(Modifier.PUBLIC, Modifier.ABSTRACT)
                .returns(returnType);

        MethodSpec.Builder methodImpl = MethodSpec.methodBuilder(name)
                .addModifiers(Modifier.PUBLIC)
                .returns(returnType);

        ps.forEach(info -> {
            method.addParameter(info.typeName, info.name);
            methodImpl.addParameter(info.typeName, info.name);
        });

        srv.addMethod(method.build());

        return methodImpl;
    }

    public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {


        /**
         * 是否启用缓存,当启用缓存的时候需要配置缓存类型,查询时会对结果进行缓存，下次再查询时先去缓存中查找
         * spring:
         *   cache:
         *     type: none
         * #    type: simple #redis etc
         */
        userCacheManager = false;

        // 配置需要生成的表名, 非必填
        includeTables = new String[]{"wl_ov_app_t_vehicle_travel"};

        // 配置需要排除的表名，注意，如果与includes同时配置，则只有includes生效，非必填
        // excludeTables = new String[]{"wl_ov_app_t_vehicle_travel"};

        // 用户名
        username = "root";

        // 密码
        password = "12345678";

        // 数据库路径
        url = "jdbc:mysql://127.0.0.1:3306/vehicle_manage_system";

        // 生成的包名会自动在指定的包名中添加 .dqsl 目的是防止跟现有的包冲突，如当前配置的包名为 xx.yy， 则生成的时候自动转为 xx.yy.qdsl
        // 可以通过设置 useQdslPackageSuffix = false 禁用
        new TableFactory().createJavaFiles("./src/main/java", "com.car.mgr.web");

        if(userCacheManager) {
            System.out.println("生成文件完毕，请在配置文件中配置相关缓存");
        }
    }

}
